package team.hyznrj.studentsys.utils;

import java.io.FileInputStream;
import java.io.IOException;
import java.text.DateFormat;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.function.Predicate;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.FormulaEvaluator;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;

import lombok.extern.java.Log;
import team.hyznrj.studentsys.entity.ExcelHeader;
import team.hyznrj.studentsys.entity.ExcelObj;
import team.hyznrj.studentsys.exception.DataIllegalException;
import team.hyznrj.studentsys.exception.ExcelIndexOutOfRangeException;
import team.hyznrj.studentsys.exception.NullExcelHeaderException;

/**
 * 读Excel 操作包括: 1.读表头(可设置必读项和可读项) 2.读excel
 * 
 * @ClassName ReadExcel
 * @author 郑泽锋
 * @date 2018年04月11日
 */
@Log
public class ReadExcel {
	private static final String EMPTYSTR = "";
	/**
	 * 头文件最大行
	 */
	private static final int TRVELSAL_NULL_HEADER_MAX_ROW_NUM = 20;
	/**
	 * 头文件最大列
	 */
	private static final int TRVELSAL_NULL_HEADER_MAX_COL_NUM = 5;
	/**
	 * 最大行搜索宽度
	 */
	private static final int TRVELSAL_NULL_ROW_NUM = 5;
	/** lambda */
	public static final Predicate<MultipartFile> EXCEL_PREDICATE=(x)->{
		return isExcel(x);
	};
	/**
	 * 判断是否为excel文件
	 * @return
	 */
	public static boolean isExcel(MultipartFile file){
		String name = file.getOriginalFilename();
		String suffix=name.substring(name.lastIndexOf(".")+1, name.length());
		log.info("excel suffix:"+suffix);
		if(!suffix.equals("xls")&&!suffix.equals("xlsx")){
			throw new DataIllegalException("请上传以.xls/.xlsx为后缀的文件");
		}
		if(file.isEmpty()){
			throw new DataIllegalException("文件内容不能为空(File Empty)");
		}
		//TODO excel的后缀不止这些
//		if(!(file.getName().endsWith(".xls")||file.getName().endsWith(".xlsx"))){
//			throw new DataIllegalException("上传的文件必须为excel文件");
//		}
		return true;
	}
	/**
	 * 通过行列查询数据,返回第row行第col列的数据，默认数据组0
	 * 
	 * @param row
	 * @param col
	 * @param readSheet
	 * @return
	 * @throws DataIllegalException
	 */
	private String getValue(int row, int col, Sheet readSheet, Workbook formatSuppose) throws DataIllegalException {
		String cellValue = null;
		if (readSheet != null && row >= 0 && col >= 0) {
			Row row2 = readSheet.getRow(row);
			if (row2 == null) {
				return null;
			}
			Cell cell = row2.getCell(col);
			if (cell == null) {
				return null;
			}
			switch (cell.getCellType()) {
			case HSSFCell.CELL_TYPE_NUMERIC: { // 数字
				if (0 == cell.getCellType()) {// 判断单元格的类型是否则NUMERIC类型
					if (HSSFDateUtil.isCellDateFormatted(cell)) {// 判断是否为日期类型
						Date date = cell.getDateCellValue();
						DateFormat formater = new SimpleDateFormat("yyyy-MM-dd");
						cellValue = formater.format(date);
					} else {
						// //如果是以 #.0结尾，则设置为#
						double value = cell.getNumericCellValue();
						/* 如果字符串是以ABC.0结尾，则转化为ABC(整数化) */
						String tmp = String.valueOf(value);
						if (tmp.matches("^.*?\\.0$")) {
							cellValue = String.format("%.0f", value);
							if (cellValue.indexOf(".") > 0) {
								cellValue = cellValue.replaceAll("0+?$", "");// 去掉多余的0
								cellValue = cellValue.replaceAll("[.]$", "");// 如最后一位是.则去掉
							}
						} else {
							/* 如果包含E,所以为科学计数法(几乎不存在的情况) */
							if (tmp.matches("^.*?E.*$")) {
								DecimalFormat df = new DecimalFormat("#");
								cellValue = df.format(value);
							} else {
								/* 没有任何情况则保留原值 */
								cellValue = String.valueOf(value);
								if (cellValue.indexOf(".") > 0) {
									cellValue = cellValue.replaceAll("0+?$", "");// 去掉多余的0
									cellValue = cellValue.replaceAll("[.]$", "");// 如最后一位是.则去掉
								}
							}
						}
					}
					break;
				}
			}
			case HSSFCell.CELL_TYPE_STRING: { // 字符串
				cellValue = cell.getStringCellValue();
				break;
			}
			case HSSFCell.CELL_TYPE_BOOLEAN: { // Boolean
				cellValue = cell.getBooleanCellValue() + "";
				break;
			}
			case HSSFCell.CELL_TYPE_FORMULA: { // 公式
				FormulaEvaluator evaluator = formatSuppose.getCreationHelper().createFormulaEvaluator();
				if (cell != null) {
					switch (evaluator.evaluateInCell(cell).getCellType()) {
					case Cell.CELL_TYPE_BOOLEAN: {
						cellValue = String.valueOf(cell.getBooleanCellValue());
						break;
					}
					case Cell.CELL_TYPE_NUMERIC: {
						cellValue = String.valueOf(cell.getNumericCellValue());
						break;
					}
					case Cell.CELL_TYPE_STRING: {
						cellValue = cell.getStringCellValue();
						break;
					}
					case Cell.CELL_TYPE_BLANK: {
						cellValue = "";
						break;
					}
					case Cell.CELL_TYPE_ERROR: {
						cellValue = String.valueOf(cell.getErrorCellValue());
						break;
					}
					// CELL_TYPE_FORMULA will never occur
					case Cell.CELL_TYPE_FORMULA: {
						break;
					}
					}
				}
				break;
			}
			case HSSFCell.CELL_TYPE_BLANK: { // 空值
				cellValue = "";
				break;
			}
			case HSSFCell.CELL_TYPE_ERROR: { // 故障
				throw new DataIllegalException("Excel CELL_TYPE_ERROR");
			}
			default: {
				cellValue = null;
				break;
			}
			}
		} else {
			throw new DataIllegalException("Excel参数非法");
		}

		return cellValue;

	}

	/**
	 * 读取文件，通过路径
	 * @throws Exception 
	 */
	public ExcelObj readAll(String directPath, int sheetAt) throws Exception {
		ExcelHeader header = this.readWorkSheetHeader(directPath, sheetAt);
		ExcelObj obj = this.readWorkSheet(directPath, header, sheetAt);
		return obj;
	}

	/**
	 * 读取directPath的文件，需要header头文件和操作数据库的下表
	 * 
	 * @param directPath
	 * @param header
	 * @param sheetAt
	 * @return
	 * @throws IOException 
	 * @throws DataIllegalException 
	 * @throws NullExcelHeaderException 
	 * @throws ExcelIndexOutOfRangeException 
	 * @throws Exception 
	 */
	public ExcelObj readWorkSheet(String directPath, ExcelHeader head, int sheetAt) throws IOException, DataIllegalException, NullExcelHeaderException, ExcelIndexOutOfRangeException {
		ArrayList<String> header = null;
		header = head.getHeader();
		if (header == null || header.size() <= 0) {
			/**
			 * 如果头文件为空不操作
			 */
			throw new NullExcelHeaderException("Excel表头为空");
		}
		log.info("header:"+header);
		/* 对象初始化 */
		ArrayList<Boolean> headerFlag = head.getHeaderFlag();
		int realIndex = 0;
		for (int i = 0; i < headerFlag.size(); i++) {
			if (headerFlag.get(i)) {
				realIndex++;
			}
		}
		boolean error = false;
		ExcelObj obj = new ExcelObj();
		obj.setHeader(head);
		Workbook execelObject = null;
		Sheet readSheet = null;
		FileInputStream in = null;
		try {
			/* poi包读取excel,判断版本 */
			in = new FileInputStream(directPath);
			String tmp = ".xls";
			if (directPath.endsWith(tmp)) {
				execelObject = new HSSFWorkbook(in);
			} else {
				execelObject = new XSSFWorkbook(in);
			}
			
			if(sheetAt>execelObject.getNumberOfSheets()-1){
				execelObject.close();
				throw new ExcelIndexOutOfRangeException();
			}
			readSheet = execelObject.getSheetAt(sheetAt);
			int countRow = 0;
			int countCol = 0;
			boolean flag = false;
			/* 预读表头，节省接下来的读取操作 */
			int[] index = new int[header.size()];
			for (int i = 0; i < index.length; i++) {
				index[i] = -1;
			}
			int indexNum = 0;
			/* 先行广度读取,后列广度读取,读取长度取决于常量 */
			for (int row = 0; !flag; row++) {
				if (countRow < TRVELSAL_NULL_HEADER_MAX_ROW_NUM) {
					indexNum = 0;
					countCol = 0;
					for (int col = 0;; col++) {
						/* 取值 */
						// TODO
						String value = getValue(row, col, readSheet, execelObject);
						/* 非空判断 */
						if (value != null && !EMPTYSTR.equals(value.trim())) {
							/* 存值 */	
							log.info(row+","+col+":"+value);
							for (int i = 0; i < header.size(); i++) {
								/* 虑空条件下,获得表头所需数据 */
								if (header.get(i).equals(value.replaceAll(" ", "")) && index[i] == -1) {
									index[i] = col;
									if (headerFlag.get(i)) {
										indexNum++;
									}
								}
								/* 长度上限 */
								if (indexNum == realIndex) {
									/* 只有这种状态下才会读表 */
									flag = true;
									break;
								}
							}
						} else {
							/* 行读为空,达到常量上限，转读下一行 */
							if (countCol < TRVELSAL_NULL_HEADER_MAX_COL_NUM) {
								countCol++;
							} else {
								break;
							}

						}
					}
					countRow++;
				} else {
					/* 退出循环 */
					break;
				}
			}

			if (flag) {
				/* 正常退出,说明取得表头下标数据 */
				int nullTimes = 0;
				for (int row = countRow;; row++) {
					ArrayList<String> rowList = new ArrayList<String>();
					int count = 0;
					for (int i = 0; i < header.size(); i++) {
						// String value = getValue(row, index[i], readSheet);
						String value = null;
						if (index[i] == -1) {
							value = "";
						} else {
							// TODO
							value = getValue(row, index[i], readSheet, execelObject);
						}
						if (value != null && !EMPTYSTR.equals(value.trim())) {
							rowList.add(value.replaceAll(" ", ""));
						} else {
							/* 虑空 */
							rowList.add("");
							count++;
						}
					}
					/* 多次虑空 */
					if ((count - header.size()) <= 1 && (count - header.size()) >= 0) {
						nullTimes++;
						if (nullTimes >= TRVELSAL_NULL_ROW_NUM) {
							break;
						}
					} else {
						/* 取行值 */
						obj.put(rowList);
					}
				}
//				if (obj.getList().size() > 0) {
//					/* 放置表头 */
//					obj.put(0, header);
//				}
			} else {
				/* 如果获取不到完整表头，则不进行任何动作 */
				error = true;
			}
		}finally {
			if (in != null) {
				try {
					in.close();
				} catch (IOException e) {
					e.printStackTrace();
				}
			}
		}
		if (error == true) {
			throw new NullExcelHeaderException("表头获取数据不存在");
		}
		return obj;

	}

	/**
	 * 返回自定义的flag数组 readWorkSheetHeader
	 * 
	 * @param directPath
	 * @param flag
	 * @param sheetAt
	 * @return
	 * @return ExcelHeader
	 */
	public ExcelHeader readWorkSheetHeader(String directPath, ArrayList<Boolean> flag, int sheetAt) {
		ExcelHeader header = readWorkSheetHeader(directPath, sheetAt);
		header.setHeaderFlag(flag);
		return header;
	}

	/**
	 * 获取文件头
	 * 
	 * @param directPath
	 * @param sheetAt
	 * @return
	 */
	public ExcelHeader readWorkSheetHeader(String directPath, int sheetAt) {
		ExcelHeader header = new ExcelHeader();
		Workbook execelObject = null;
		Sheet readSheet = null;
		FileInputStream in = null;
		try {
			in = new FileInputStream(directPath);
			String tmp = ".xls";
			if (directPath.endsWith(tmp)) {
				execelObject = new HSSFWorkbook(in);
				readSheet = ((HSSFWorkbook) execelObject).getSheetAt(sheetAt);
			} else {
				execelObject = new XSSFWorkbook(in);
			}
			readSheet = execelObject.getSheetAt(sheetAt);
			for (int col = 0;; col++) {
				// TODO
				String value = getValue(0, col, readSheet, execelObject);
				if (value != null && !EMPTYSTR.equals(value.trim())) {
					header.append(value.replaceAll(" ", ""));
				} else {
					break;
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return header;
	}
}
